package com.dao.Fxy;


import com.bean.Fxy.Dygl;
import com.bean.Fxy.Lygl;
import com.bean.Fxy.Tjxx;
import com.bean.Fxy.Xqgl;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Repository;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class GetData {

    private String sql;

    /**已完成
     * 加载字段
     * @return
     */
    public Object getall(){
        Connection con=Mysql.getConnection();
              sql="SELECT * FROM village";
        QueryRunner queryRunner=new QueryRunner();
       try{
           return queryRunner.query(con,sql,new MapListHandler());
       }catch (SQLException e){
           e.printStackTrace();
       }
       return null;
    }

    /**已完成
     * 点击查询
     * @param
     * @return
     */
    public List<Xqgl> lgetListxqgl (String inp) {
        QueryRunner qr = new QueryRunner();
        Connection conn =Mysql.getConnection();
        String sql = "select * from village where vname  like ?";
        Object[] objects = new Object[]{"%"+inp+"%"};
        List<Xqgl> list = null;
        try {
            System.out.println("12");
            list = qr.query(conn, sql,objects, new BeanListHandler<Xqgl>(Xqgl.class));
            System.out.println(list);
            if(list!=null){
                return  list;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return null;
    }

//    public Object lgetListxqgl(String vid){
//        Connection con=Mysql.getConnection();
//        sql="SELECT * FROM village WHERE vid=?";
//        QueryRunner queryRunner=new QueryRunner();
//        try {
//            return queryRunner.query(con,sql,new MapListHandler(),vid);
//        }catch (SQLException e){
//            e.printStackTrace();
//        }
//        return null;
//    }
    /**已完成
     * 点击删除
     *
     */
    public boolean getsc(String vid){

        System.out.println("vid:"+vid);

        if(scly(vid)){
            return false;
        }else {


            Connection con = Mysql.getConnection();


            sql = "DELETE FROM village WHERE vid=?";
            QueryRunner queryRunner = new QueryRunner();
            try {
                int i = queryRunner.update(con, sql, vid);
                if (i != 0) {
                    return true;
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 判断是否能删除楼宇
     */
    public Boolean scly(String  vid){
        Connection con=Mysql.getConnection();



        try {
            String  sql="SELECT * FROM  village v,tenement t  where v.vid=t.vid and t .vid=?";
            PreparedStatement pr= con.prepareStatement(sql);
            pr.setString(1,vid);

            ResultSet rs= pr.executeQuery();

            if(rs.next()){
                return true;
            }else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        return  false;

    }


    /**
     * 模态框添加小区信息
     */
  public int getTjxx(String xqmc, String szdq, String xxdz,
                     String kfdh, String fzr, String lxrdh, String zdmj, String jzmj, String ggcsmj,
                     String lhmj, String sjcwmj, String ckmj, Integer cws, String bz, String tpname){
   Connection con=Mysql.getConnection();
   sql="INSERT INTO village VALUES (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
   QueryRunner queryRunner=new QueryRunner();
   Object [] o =new Object[]{
          xqmc,szdq,xxdz,kfdh,fzr,lxrdh,tpname,zdmj,jzmj,ggcsmj,lhmj,sjcwmj,ckmj,cws,bz
   };
   try {
    return queryRunner.update(con,sql,o);
   }catch (SQLException e){
       e.printStackTrace();
   }finally {
       try {
           con.close();
       } catch (SQLException e) {
           throw new RuntimeException(e);
       }
   }
   return 0;
  }
    /**
     * 模态框添加小区信息照片
     */
public int getXgxq( String tp,String xqmc, String szdq, String xxdz,
                    String kfdh, String fzr, String lxrdh, String zdmj, String jzmj, String ggcsmj,
                    String lhmj, String sjcwmj, String ckmj, Integer cws, String bz,String vid){
    Connection con=Mysql.getConnection();
    sql="UPDATE village SET vname = ?,varea = ?,vaddress = ?,vcustomer = ?,vleading = ?,vlinkman = ?,vbackdrop = ?,vcover = ?,vbuilding = ?,vplace = ?,vtrees = ?,vspace = ?,vgarage = ?,vdigit = ?,vremark = ? WHERE vid = ?";

    QueryRunner queryRunner=new QueryRunner();
    Object[] o=new Object[]{
            xqmc,szdq,xxdz,kfdh,fzr,lxrdh,tp,zdmj,jzmj,ggcsmj,lhmj,sjcwmj,ckmj,cws,bz,vid
    };
    try {
        return queryRunner.update(con,sql,o);
    }catch (SQLException e){
        e.printStackTrace();
    }finally {
        try {
            con.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    return 0;
}
    //楼宇管理

    /**
     * 加载字段
     * @return
     */
    public Object getXszd(){
    Connection con=Mysql.getConnection();
    sql="select t.*,v.vname from tenement t inner join village v on t.vid=v.vid";
    QueryRunner queryRunner=new QueryRunner();
    try {
        return queryRunner.query(con,sql,new MapListHandler());
}catch (SQLException e){
        e.printStackTrace();
    }finally {
        try {
            con.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
      return  null;
    }
    /**
     * 点击删除
     */
    public  boolean getDjsc(String tid){
        Connection con=Mysql.getConnection();
        sql="DELETE FROM tenement WHERE tid=?";
        QueryRunner queryRunner=new QueryRunner();
        try {
            int i= queryRunner.update(con,sql,tid);
            if(i != 0){
                return true;
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return false;

    }
    /**
     * 模态框添加小区
     */
    public int getLygl(Lygl lytj){
        Connection con=Mysql.getConnection();
        sql="INSERT INTO tenement VALUES (null,?,?,?,?,?,?,?,?)";
        QueryRunner queryRunner=new QueryRunner();
        Object[]o=new Object[]{
                lytj.getVid(),lytj.getTtname(),lytj.getTnumber(),lytj.getTstorey(),lytj.getTgenre(),lytj.getTstructure(),
                lytj.getTdirection(),lytj.getTremarks(),
        };
        try {
            return  queryRunner.update(con,sql,o);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return  0;
    }
    /**
     * 模态框修改小区
     */
    public int getXuiga(Lygl lytj){
     Connection con=Mysql.getConnection();
     sql="UPDATE  tenement SET  vid=?,ttname=?,tnumber=?,tstorey=?,tgenre=?,tstructure=?,tdirection=?,tremarks=? WHERE tid=?";
      QueryRunner queryRunner=new QueryRunner();
        System.out.println();
      Object[]o=new Object[]{
              lytj.getVid(),lytj.getTtname(),lytj.getTnumber(),lytj.getTstorey(),lytj.getTgenre(),lytj.getTstructure(),
              lytj.getTdirection(),lytj.getTremarks(),lytj.getTid(),
      };
      try {
          return  queryRunner.update(con,sql,o);
      }catch (SQLException e){
          e.printStackTrace();
      }finally {
          try {
              con.close();
          } catch (SQLException e) {
              throw new RuntimeException(e);
          }
      }
      return 0;
    }
/**
// * 修改信息显示字段
// */
//    public Object getXiugai(){
//        Connection con=Mysql.getConnection();
//        sql="";
//        QueryRunner queryRunner=new QueryRunner();
//          try {
//              return queryRunner.query(con,sql,new MapListHandler());
//          }catch (SQLException e){
//              e.printStackTrace();
//          }
//          return null;
//    }
//
    /**
     * 单元
     * @return
     */
    public Object getDanyuan(Dygl dygl){
        Connection con=Mysql.getConnection();
        sql="select v.vname,t.ttname,u.uname from  tenement t " +
                "INNER JOIN  unit u INNER JOIN village v on t.tid=u.tid and t.vid=v.vid where t.tid= ? ;";
        QueryRunner queryRunner=new QueryRunner();
        Object[]o=new Object[]{
              dygl.getTid()
        };
        try {
            return  queryRunner.query(con,sql,new BeanListHandler<>(Dygl.class),o);

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
            return  null;
    };
    /**
     * 楼宇查询
     */
    public List<Lygl>getChaxun(String inpu){
        QueryRunner Qu=new QueryRunner();
        Connection conn=Mysql.getConnection();
        sql="select t.*,v.vname from tenement t inner join village v on t.vid=v.vid where ttname  like ?";
        Object[]objects=new Object[]{"%"+inpu+"%"};
        List<Lygl>list=null;
        try{
            list=Qu.query(conn,sql,objects,new BeanListHandler<Lygl>(Lygl.class));
            System.out.println(list);
            if(list!=null){
                return list;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return null;
    }

    /**
     * 判断是否能删除
     */
    public boolean getshanchu1(int vid){
        QueryRunner queryRunner=new QueryRunner();
        Connection con=Mysql.getConnection();
        sql="";
        try {
            int i=queryRunner.update(con,sql,vid);
                    if(i !=0){
                    return true;
                    }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return false;
    }
}

